using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace AttributesFramework.Data
{
    public class Provider
    {
        private static string _connectionString = String.Empty;
        public static string connectionString
        {
            get
            {
                if (_connectionString == String.Empty)
                {
                    if (System.Configuration.ConfigurationManager.ConnectionStrings["Database"] == null)
                        throw new Exception("Error: ConnectionString not found in .config!");

                    _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
                }

                return _connectionString;                
            }
        }

        public static int ExecuteNonQuery(CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            SqlCommand _command = new SqlCommand();
            int _return = 0;

            try
            {
                using (SqlConnection _connection = CreateConnection())
                {
                    CreateCommand(_command, _connection, p_CommandType, p_CommandText, p_Parameters);
                    _return = _command.ExecuteNonQuery();
                    _command.Parameters.Clear();
                    EndConnection(_connection, _command);
                }

                return _return;
            }
            catch (Exception e)
            {
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }

        }

        public static int ExecuteNonQuery(SqlTransaction p_Transaction, CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            try
            {                
                SqlCommand _command = new SqlCommand();
                CreateCommand(_command, p_Transaction, p_CommandType, p_CommandText, p_Parameters);
                int _return = _command.ExecuteNonQuery();
                _command.Parameters.Clear();

                return _return;
            }
            catch (Exception e)
            {
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }
        }

        public static SqlDataReader ExecuteReader(SqlTransaction p_Transaction, CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            try
            {
                SqlCommand _command = new SqlCommand();
                CreateCommand(_command, p_Transaction, p_CommandType, p_CommandText, p_Parameters);
                SqlDataReader dr = _command.ExecuteReader();
                _command.Parameters.Clear();

                return dr;
            }
            catch (Exception e)
            {
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }
        }

        public static SqlDataReader ExecuteReader(CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            SqlCommand _command = new SqlCommand();
            SqlConnection _connection = CreateConnection();

            try
            {
                CreateCommand(_command, _connection, p_CommandType, p_CommandText, p_Parameters);
                SqlDataReader dr = _command.ExecuteReader();
                _command.Parameters.Clear();

                return dr;
            }
            catch (Exception e)
            {
                EndConnection(_connection, _command);
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }
        }

        public static object ExecuteScalar(CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            try
            {
                SqlCommand _command = new SqlCommand();

                object _return = null;

                using (SqlConnection _connection = CreateConnection())
                {
                    CreateCommand(_command, _connection, p_CommandType, p_CommandText, p_Parameters);
                    _return = _command.ExecuteScalar();
                    _command.Parameters.Clear();
                    EndConnection(_connection, _command);
                }

                return _return;
            }
            catch (Exception e)
            {
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }
        }

        public static object ExecuteScalar(SqlTransaction p_Transaction, CommandType p_CommandType, string p_CommandText, params SqlParameter[] p_Parameters)
        {
            //System.IO.File.AppendAllText(@"C:\DEBUG.TXT", String.Format("{0}\r\n", p_CommandText));

            try
            {
                SqlCommand _command = new SqlCommand();

                object _return = null;

                CreateCommand(_command, p_Transaction, p_CommandType, p_CommandText, p_Parameters);
                _return = _command.ExecuteScalar();
                _command.Parameters.Clear();

                return _return;
            }
            catch (Exception e)
            {
                throw new Exception(ErrorMessage(e, p_CommandType, p_CommandText, p_Parameters));
            }
        }

        private static string ErrorMessage(Exception e, CommandType p_CommandType, string p_CommandText, SqlParameter[] p_Parameters)
        {
            return e.Message + " | " + p_CommandText + "(" + p_CommandType.ToString() + ")";
        }

        private static void CreateCommand(SqlCommand p_Command, SqlTransaction p_Transaction, CommandType p_CommandType, string p_CommandText, SqlParameter[] p_Parameters)
        {
            if (p_Transaction.Connection.State != ConnectionState.Open)
                p_Transaction.Connection.Open();

            p_Command.Connection = p_Transaction.Connection;
            p_Command.CommandText = p_CommandText;

            if (p_Transaction != null)
                p_Command.Transaction = p_Transaction;

            p_Command.CommandType = p_CommandType;

            if (p_Parameters != null)
            {
                foreach (SqlParameter param in p_Parameters)
                    p_Command.Parameters.Add(param);
            }
        }

        private static void CreateCommand(SqlCommand p_Command, SqlConnection p_Connection, CommandType p_CommandType, string p_CommandText, SqlParameter[] p_Parameters)
        {
            if (p_Connection.State != ConnectionState.Open)
                p_Connection.Open();

            p_Command.Connection = p_Connection;
            p_Command.CommandText = p_CommandText;

            p_Command.CommandType = p_CommandType;

            if (p_Parameters != null)
            {
                foreach (SqlParameter param in p_Parameters)
                    p_Command.Parameters.Add(param);
            }
        }

        public static SqlConnection CreateConnection()
        {
            SqlConnection _connection = new SqlConnection(connectionString);
            _connection.Open();
            return _connection;
        }

        public static void EndConnection(SqlConnection p_Connection, SqlCommand p_Command)
        {
            if (p_Connection != null)
            {
                if (p_Connection.State == System.Data.ConnectionState.Open)
                    p_Connection.Close();

                p_Connection.Dispose();
            }

            if (p_Command != null)
                p_Command.Dispose();
        }

        public static void EndConnection(SqlConnection p_Connection, SqlDataReader p_DataReader, SqlCommand p_Command)
        {
            if (p_Connection != null)
            {
                if (p_Connection.State == System.Data.ConnectionState.Open)
                    p_Connection.Close();

                p_Connection.Dispose();
            }

            if (p_DataReader != null)
            {
                if (!p_DataReader.IsClosed)
                    p_DataReader.Close();
            }

            if (p_Command != null)
                p_Command.Dispose();
        }

        public static void EndConnection(SqlConnection p_Connection, SqlDataAdapter p_DataAdapter)
        {
            if (p_Connection != null)
            {
                if (p_Connection.State == System.Data.ConnectionState.Open)
                    p_Connection.Close();

                p_Connection.Dispose();
            }
            if (p_DataAdapter != null)
                p_DataAdapter.Dispose();
        }

        public static void EndConnection(SqlConnection p_Connection, DataSet p_DataSet, SqlDataAdapter p_DataAdapter)
        {
            if (p_Connection != null)
            {
                if (p_Connection.State == System.Data.ConnectionState.Open)
                    p_Connection.Close();

                p_Connection.Dispose();
            }
            if (p_DataSet != null)
                p_DataSet.Dispose();

            if (p_DataAdapter != null)
                p_DataAdapter.Dispose();
        }

        public static void EndConnection(SqlConnection p_Connection, SqlDataAdapter p_DataAdapter, SqlCommand p_Command)
        {
            if (p_Connection != null)
            {
                if (p_Connection.State == System.Data.ConnectionState.Open)
                    p_Connection.Close();

                p_Connection.Dispose();
            }

            if (p_DataAdapter != null)
                p_DataAdapter.Dispose();

            if (p_Command != null)
                p_Command.Dispose();
        }
    }
}
